package com.ruoyi.util;

import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * 数据库创建工具
 * 用于在Spring Boot启动前创建数据库
 * 
 * @author Saumell
 */
//@Component  // 数据库已创建，禁用此组件
@Order(0)  // 最高优先级执行
public class DatabaseCreator implements CommandLineRunner {

    private static final String HOST = "db.test1.wcsbapp.com";
    private static final int PORT = 3306;
    private static final String USERNAME = "admin";
    private static final String PASSWORD = "0YvoGUzwhumnf2KGQvjN";
    private static final String DATABASE_NAME = "ChainUp";

    @Override
    public void run(String... args) throws Exception {
        try {
            createDatabase();
            System.out.println("✅ 数据库创建完成");
        } catch (Exception e) {
            System.err.println("❌ 数据库创建失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        try {
            new DatabaseCreator().createDatabase();
            System.out.println("✅ 数据库创建完成，可以启动Spring Boot应用了");
        } catch (Exception e) {
            System.err.println("❌ 数据库创建失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public void createDatabase() throws Exception {
        // 加载MySQL驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        
        // 首先连接到MySQL服务器（不指定数据库）
        String connectionUrl = String.format("jdbc:mysql://%s:%d/?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%%2B8", HOST, PORT);
        
        System.out.println("连接到MySQL服务器: " + HOST + ":" + PORT);
        
        try (Connection connection = DriverManager.getConnection(connectionUrl, USERNAME, PASSWORD);
             Statement statement = connection.createStatement()) {
            
            // 创建数据库
            String createDbSql = String.format("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", DATABASE_NAME);
            statement.execute(createDbSql);
            System.out.println("✓ 数据库 " + DATABASE_NAME + " 创建成功");
            
            // 切换到新创建的数据库
            statement.execute("USE " + DATABASE_NAME);
            System.out.println("✓ 切换到数据库 " + DATABASE_NAME);
            
            // 创建基础表结构
            createTables(statement);
            
            System.out.println("✓ 数据库初始化完成");
        }
    }
    
    private static void createTables(Statement stmt) throws Exception {
        System.out.println("开始创建数据表...");
        
        // 1. 创建sys_user表
        String sysUserSql = "CREATE TABLE IF NOT EXISTS sys_user (" +
                "user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID'," +
                "dept_id BIGINT NULL COMMENT '部门ID'," +
                "user_name VARCHAR(30) NOT NULL COMMENT '用户账号'," +
                "nick_name VARCHAR(30) NOT NULL COMMENT '用户昵称'," +
                "user_type VARCHAR(2) DEFAULT '00' COMMENT '用户类型（00系统用户）'," +
                "email VARCHAR(50) DEFAULT '' COMMENT '用户邮箱'," +
                "phonenumber VARCHAR(11) DEFAULT '' COMMENT '手机号码'," +
                "sex CHAR(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）'," +
                "avatar VARCHAR(100) DEFAULT '' COMMENT '头像地址'," +
                "password VARCHAR(100) DEFAULT '' COMMENT '密码'," +
                "status CHAR(1) DEFAULT '0' COMMENT '帐号状态（0正常 1停用）'," +
                "del_flag CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）'," +
                "login_ip VARCHAR(128) DEFAULT '' COMMENT '最后登录IP'," +
                "login_date DATETIME NULL COMMENT '最后登录时间'," +
                "create_by VARCHAR(64) DEFAULT '' COMMENT '创建者'," +
                "create_time DATETIME NULL COMMENT '创建时间'," +
                "update_by VARCHAR(64) DEFAULT '' COMMENT '更新者'," +
                "update_time DATETIME NULL COMMENT '更新时间'," +
                "remark VARCHAR(500) DEFAULT NULL COMMENT '备注'," +
                "PRIMARY KEY (user_id)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT = '用户信息表'";
        stmt.execute(sysUserSql);
        System.out.println("✓ sys_user表创建成功");

        // 2. 创建monitor_result表
        String monitorResultSql = "CREATE TABLE IF NOT EXISTS monitor_result (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                "api_name VARCHAR(255) NOT NULL COMMENT 'API名称'," +
                "api_type VARCHAR(50) NOT NULL COMMENT 'API类型(spot/swap/futures)'," +
                "request_url VARCHAR(500) NOT NULL COMMENT '请求URL'," +
                "request_method VARCHAR(10) DEFAULT 'GET' COMMENT '请求方法'," +
                "response_status INT NULL COMMENT '响应状态码'," +
                "response_time BIGINT NULL COMMENT '响应时间(毫秒)'," +
                "response_body TEXT NULL COMMENT '响应内容'," +
                "error_message TEXT NULL COMMENT '错误信息'," +
                "check_time DATETIME NOT NULL COMMENT '检查时间'," +
                "is_success TINYINT(1) DEFAULT 0 COMMENT '是否成功(0失败 1成功)'," +
                "created_by VARCHAR(64) DEFAULT 'system' COMMENT '创建者'," +
                "created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "updated_by VARCHAR(64) DEFAULT 'system' COMMENT '更新者'," +
                "updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                "PRIMARY KEY (id)," +
                "INDEX idx_api_name (api_name)," +
                "INDEX idx_check_time (check_time)," +
                "INDEX idx_api_type (api_type)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='API监控结果表'";
        stmt.execute(monitorResultSql);
        System.out.println("✓ monitor_result表创建成功");

        // 3. 创建websocket_monitor表
        String websocketMonitorSql = "CREATE TABLE IF NOT EXISTS websocket_monitor (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                "session_id VARCHAR(255) NOT NULL COMMENT 'WebSocket会话ID'," +
                "connection_type VARCHAR(50) NOT NULL COMMENT '连接类型(spot/swap/futures)'," +
                "symbol VARCHAR(50) NULL COMMENT '交易对'," +
                "channel VARCHAR(100) NULL COMMENT '订阅频道'," +
                "connect_time DATETIME NOT NULL COMMENT '连接时间'," +
                "disconnect_time DATETIME NULL COMMENT '断开时间'," +
                "status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '连接状态(ACTIVE/DISCONNECTED/ERROR)'," +
                "last_heartbeat DATETIME NULL COMMENT '最后心跳时间'," +
                "message_count BIGINT DEFAULT 0 COMMENT '消息数量'," +
                "error_count BIGINT DEFAULT 0 COMMENT '错误数量'," +
                "error_message TEXT NULL COMMENT '错误信息'," +
                "created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                "PRIMARY KEY (id)," +
                "INDEX idx_session_id (session_id)," +
                "INDEX idx_connection_type (connection_type)," +
                "INDEX idx_connect_time (connect_time)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='WebSocket连接监控表'";
        stmt.execute(websocketMonitorSql);
        System.out.println("✓ websocket_monitor表创建成功");

        // 4. 创建sys_config表
        String sysConfigSql = "CREATE TABLE IF NOT EXISTS sys_config (" +
                "config_id INT NOT NULL AUTO_INCREMENT COMMENT '参数主键'," +
                "config_name VARCHAR(100) DEFAULT '' COMMENT '参数名称'," +
                "config_key VARCHAR(100) DEFAULT '' COMMENT '参数键名'," +
                "config_value VARCHAR(500) DEFAULT '' COMMENT '参数键值'," +
                "config_type CHAR(1) DEFAULT 'N' COMMENT '系统内置（Y是 N否）'," +
                "create_by VARCHAR(64) DEFAULT '' COMMENT '创建者'," +
                "create_time DATETIME NULL COMMENT '创建时间'," +
                "update_by VARCHAR(64) DEFAULT '' COMMENT '更新者'," +
                "update_time DATETIME NULL COMMENT '更新时间'," +
                "remark VARCHAR(500) DEFAULT NULL COMMENT '备注'," +
                "PRIMARY KEY (config_id)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT = '参数配置表'";
        stmt.execute(sysConfigSql);
        System.out.println("✓ sys_config表创建成功");

        // 插入初始数据
        insertInitialData(stmt);
    }
    
    private static void insertInitialData(Statement stmt) throws Exception {
        System.out.println("插入初始数据...");
        
        // 插入默认管理员用户
        String userSql = "INSERT INTO sys_user (user_id, user_name, nick_name, password, status, create_by, create_time) " +
                "VALUES (1, 'admin', '管理员', '$2a$10$7JB720yubVSOfvam/u3aYeBPF.JRw6KgWWj5YX1.WGK5OZCeIGu8W', '0', 'admin', NOW()) " +
                "ON DUPLICATE KEY UPDATE user_name = user_name";
        stmt.execute(userSql);
        
        // 插入默认系统配置
        String[] configSqls = {
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('监控间隔时间', 'monitor.interval', '30', 'Y', 'admin', NOW(), '监控检查间隔时间（秒）') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key",
            
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('WebSocket重连次数', 'websocket.retry.count', '3', 'Y', 'admin', NOW(), 'WebSocket连接失败重连次数') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key",
            
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('API超时时间', 'api.timeout', '10000', 'Y', 'admin', NOW(), 'API请求超时时间（毫秒）') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key"
        };
        
        for (String sql : configSqls) {
            stmt.execute(sql);
        }
        
        System.out.println("✓ 初始数据插入成功");
    }
}